﻿Imports System.DBNull
Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class CustoDeImportacaoDespAduaneirasL
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        If Not Page.IsPostBack Then

            Dim strConnection As String
            strConnection = "A"

            lblModulo.Text = "Operações"
            lblModuloItem.Text = "Dados da Importação - Despesas Aduaneiras - LINHA"

            If (LoadHolding(strConnection) = True) Then

                FillDDL()

                Try
                    If (Session("strOperacao") = "I") Then

                    ElseIf (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                        LoadData()
                    End If

                    If (CarregaGVW() = True) Then
                        btnIncluir.Enabled = True
                    End If

                    If (Session("strOperacao") = "C") Then ' Consultar
                        btnIncluir.Enabled = False
                    End If

                Catch ex As Exception
                    lblMsg.Text = ex.Message
                End Try

            End If

        End If

    End Sub

    ' Step 02
    Private Function LoadHolding(ByVal strConn As String) As Boolean

        ' CAMG Informática | Marcelo Barcelos | 26.06.2010 08h


        LoadHolding = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT CodigoDaHolding, NomeDaHolding" & _
                         " FROM Holding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read() = True) Then
                    lblHolding.Text = theSqlDataReader(1)
                    lblHolding.ToolTip = theSqlDataReader(0)
                    LoadHolding = True
                Else
                    lblMsg.Text = "Holding não cadastrada no Sistema."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Function

    '' Step 03
    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, NomeDaCia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "NomeDaCia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodFornecedorServico, RazaoSocialFornecedorServico AS FornecedorServico" & _
                         " FROM fornecedorservico WHERE CodTipoFornecedorServico = 2" & _
                         " ORDER BY RazaoSocialFornecedorServico;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlFornecedorServico.DataTextField = "FornecedorServico"
                ddlFornecedorServico.DataValueField = "CodFornecedorServico"
                ddlFornecedorServico.DataSource = mydatatable
                ddlFornecedorServico.DataBind()

                ddlFornecedorServico.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaMoeda, SiglaDaMoeda + ' - ' + NomeDaMoeda AS Moeda" & _
                 " FROM Moeda" & _
                 " ORDER BY SiglaDaMoeda;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlMoeda.DataTextField = "Moeda"
                ddlMoeda.DataValueField = "CodigoDaMoeda"
                ddlMoeda.DataSource = mydatatable
                ddlMoeda.DataBind()

                ddlMoeda.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    '' Step 04
    Private Sub LoadData()

        Dim intQtdeItems As Integer = 0

        ddlCompanhia.Enabled = False
        ddlEstabelecimento.Enabled = False
        ddlFornecedorServico.Enabled = False
        txtNumeroDocumento.Enabled = False
        ddlDI.Enabled = False
        txtDataDocumento.Enabled = False
        txtDataRegistro.Enabled = False
        ddlMoeda.Enabled = False

        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir


        End If

        ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString
        FillDDLEstabelecimento()

        ddlEstabelecimento.SelectedValue = Session("strCodigoDoEstabelecimento").ToString
        LoadUFDestino()

        ddlFornecedorServico.SelectedValue = Session("strCodFornecedorServico").ToString
        LoadUFOrigem()

        txtNumeroDocumento.Text = Session("strNumeroDocumento").ToString
        txtDataDocumento.Text = Session("strDataDocumento").ToString

        'FillDDLDI()
        ddlDI.Text = Session("strDI").ToString

        txtDataRegistro.Text = Session("strDataRegistro")
        ddlMoeda.SelectedValue = Session("strMoeda")


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT" & _
                     " DataLancamento, DataRegistro, CodMoeda, CodTipoTributacaoII," & _
                     " CodTipoTributacaoIPI, CodTipoTributacaoPIS, CodTipoTributacaoCOFINS, CodTipoTributacaoICMS, CodTipoTributacaoICMSST"

            theQueryString = theQueryString & _
                     " FROM custoimportacao" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                     " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()




                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub


    Private Function CarregaGVW() As Boolean

        CarregaGVW = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT A.LinhaCustoImportacao, A.CodSubContratante AS CodFornecedorServicoExecutor, B.RazaoSocialFornecedorServico AS NomeFantasiaFornecedorServicoExecutor, C.DscTipoDespesa, ABS(A.ValorDespesa) AS ValorDespesa " & _
                    "FROM custoimportacaodata A, fornecedorservico B, tipodespesa C " & _
                    "WHERE A.CodSubContratante = B.CodFornecedorServico AND " & _
                    "A.CodTipoOperacao = C.CodTipoOperacao AND " & _
                    "A.CodTipoDespesa = C.CodTipoDespesa AND " & _
                    "A.CodigoDaHolding = '" & lblHolding.ToolTip & "' AND " & _
                    "A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "' AND " & _
                    "A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "' AND " & _
                    "A.CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "' AND " & _
                    "A.DI = '" & ddlDI.Text & "' AND " & _
                    "A.NumeroDocumento = '" & txtNumeroDocumento.Text & "' " & _
                    "ORDER BY A.CodigoDaCia, A.CodigoDoEstabelecimento, A.NumeroDocumento, A.LinhaCustoImportacao;"


            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwData.DataSource = mydatatable
                gvwData.DataBind()

                CarregaGVW = True

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function


    Private Sub FillDDLEstabelecimento()

        ' CAMG Informática | Marcelo Barcelos | 05.01.2010 16h45

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDoEstabelecimento, CodigoDoEstabelecimento + ' - ' + RazaoSocialDoEstabelecimento AS Estabelecimento" & _
                     " FROM Estabelecimento" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " ORDER BY CodigoDoEstabelecimento;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlEstabelecimento.DataTextField = "Estabelecimento"
                ddlEstabelecimento.DataValueField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataSource = mydatatable
                ddlEstabelecimento.DataBind()

                ddlEstabelecimento.Items.Insert(0, "")
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    '' Step 07
    'Private Sub FillDDLDI()

    '    ' CAMG Informática | Marcelo Barcelos | 05.01.2010 16h45

    '    Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '        Dim mydatatable As New DataTable
    '        Dim theQueryString As String

    '        theQueryString = "SELECT DI" & _
    '                 " FROM DIHeader" & _
    '                 " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
    '                 " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
    '                 " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'"

    '        If (Session("strOperacao") = "I") Then ' Incluir
    '            theQueryString = theQueryString & _
    '                     " AND CodSituacaoDI IN (1, 2)" & _
    '                     " AND DI NOT IN (SELECT DI FROM ControleDespesasHeader WHERE di is NOT NULL)"
    '        End If

    '        theQueryString = theQueryString & _
    '                 " ORDER BY DI;"

    '        Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
    '        theSqlCommand.CommandText = theQueryString

    '        Try
    '            theSqlConnection.Open()
    '            Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '            mydatatable.Load(theSqlDataReader)
    '            ddlDI.DataTextField = "DI"
    '            ddlDI.DataValueField = "DI"
    '            ddlDI.DataSource = mydatatable
    '            ddlDI.DataBind()

    '            ddlDI.Items.Insert(0, "")
    '            theSqlDataReader.Close()
    '        Catch ex As Exception
    '            'theSqlDataReader.Close()
    '            lblMsg.Text = "Msg DB: " & ex.Message
    '        End Try

    '    End Using

    'End Sub

    Private Sub LoadUFDestino()

        lblUFDestino.Text = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT A.CodigoDaUF, B.SiglaDaUF"

            theQueryString = theQueryString & _
                     " FROM Estabelecimento A, uf B" & _
                     " WHERE A.CodigoDaUF = B.CodigoDaUF AND A.CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If Not (IsDBNull(theSqlDataReader!SiglaDaUF) = True) Then
                        lblUFDestino.Text = theSqlDataReader!SiglaDaUF
                        lblUFDestino.ToolTip = theSqlDataReader!CodigoDaUF
                    Else
                        lblMsg.Text = "Estabelecimento sem UF informada."
                    End If

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Sub

    Private Sub LoadUFOrigem()


        lblUFOrigem.Text = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT" & _
                     " A.CodigoDaUF," & _
                     " B.SiglaDaUF"

            theQueryString = theQueryString & _
                     " FROM FornecedorServico A, uf B" & _
                     " WHERE A.CodigoDaUF = B.CodigoDaUF AND A.CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If (IsDBNull(theSqlDataReader!SiglaDaUF) = True) Then
                        lblMsg.Text = "Despachante sem UF informada."
                    Else
                        lblUFOrigem.Text = theSqlDataReader!SiglaDaUF
                        lblUFOrigem.ToolTip = theSqlDataReader!CodigoDaUF
                    End If

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Protected Sub btnIncluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncluir.Click

        Session.Add("strOperacao", "I") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(ddlCompanhia.SelectedValue))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(ddlEstabelecimento.SelectedValue))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(ddlFornecedorServico.SelectedValue))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(txtNumeroDocumento.Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(txtDataDocumento.Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(ddlDI.Text))
        Session.Add("strDataRegistro", HttpUtility.HtmlDecode(txtDataRegistro.Text))
        Session.Add("strMoeda", HttpUtility.HtmlDecode(ddlMoeda.SelectedValue))


        Response.Redirect("CustoDeImportacaoDespAduaneirasLData.aspx")

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("CustoDeImportacaoDespAduaneiras.aspx")

    End Sub



    Protected Sub gvwData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvwData.PageIndexChanging

        Try
            CarregaGVW()
            gvwData.PageIndex = e.NewPageIndex
            gvwData.DataBind()
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Protected Sub gvwData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvwData.SelectedIndexChanged

        Dim strSituacao As String = ""

        Try

            If (Session("sNM_GRUPOACESSO") = 3 Or Session("sNM_GRUPOACESSO") = 4 Or Session("sNM_GRUPOACESSO") = 5) Then
                btnIncluir.Enabled = False
                btnAlterar.Enabled = True
                btnExcluir.Enabled = True
                btnConsultar.Enabled = True
            Else

                btnConsultar.Enabled = True
                btnAlterar.Enabled = True
                btnExcluir.Enabled = True
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Protected Sub btnAlterar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlterar.Click

        Session.Add("strOperacao", "A") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(ddlCompanhia.SelectedValue))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(ddlEstabelecimento.SelectedValue))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(ddlFornecedorServico.SelectedValue))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(txtNumeroDocumento.Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(txtDataDocumento.Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(ddlDI.Text))
        Session.Add("strDataRegistro", HttpUtility.HtmlDecode(txtDataRegistro.Text))
        Session.Add("strMoeda", HttpUtility.HtmlDecode(ddlMoeda.SelectedValue))
        Session.Add("strLinhaCustoImportacao", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))


        Response.Redirect("CustoDeImportacaoDespAduaneirasLData.aspx")

    End Sub

    Protected Sub btnExcluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcluir.Click

        Session.Add("strOperacao", "E") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(ddlCompanhia.SelectedValue))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(ddlEstabelecimento.SelectedValue))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(ddlFornecedorServico.SelectedValue))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(txtNumeroDocumento.Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(txtDataDocumento.Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(ddlDI.Text))
        Session.Add("strDataRegistro", HttpUtility.HtmlDecode(txtDataRegistro.Text))
        Session.Add("strMoeda", HttpUtility.HtmlDecode(ddlMoeda.SelectedValue))
        Session.Add("strLinhaCustoImportacao", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))


        Response.Redirect("CustoDeImportacaoDespAduaneirasLData.aspx")

    End Sub
End Class
